/*=======================================================================
Creator: Jingyuan Wang, jingyuanwang@u.northwestern.edu
Date created: 				08/07/2018
Date last modified: 		
Purpose: 

Import 2010 MECS raw data


This is a subfunction called in do file 3_1_clean_MECS.do

==========================================================================*/



tempfile MECS
local year = 2006
*********************************
* Import industry code
* and save the first temp file
*********************************
import excel using "$MECSraw/`year'/Table1_1.xls", cellrange(A17:B96) sheet("Table 1.1") clear
rename A naics
rename B industry
gen order = _n

* destring the industry code
destring naics, replace
replace naics = 99 if industry == "Total"

* save
save `MECS.dta', replace

*********************************
* Table 1_1
*********************************
local cat = "firstuse"
local use "allpurpose"
local firstrow = 9
local lastrow = 96
local lastcol = "R"
local name_rows = 8
forvalues i = 1(1)2 {
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
	}
	import excel using "$MECSraw/`year'/Table1_`i'.xls", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 1.`i'") allstring clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	local num = 0
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
		quietly sum `var', meanonly
		if `r(N)' == 0 {
			disp("`var' A label column")
			drop `var'
		}
		else {
			local num = `num' + 1
			rename `var' var`num'
		}
	}
	*
	rename A naics
	rename B industry
	rename var1 `cat'_`use'_tot_`i'
	rename var2 `cat'_`use'_elec_`i'
	rename var3 `cat'_`use'_resdfuel_`i'
	rename var4 `cat'_`use'_distfuel_`i'
	rename var5 `cat'_`use'_gas_`i'
	rename var6 `cat'_`use'_hgl_`i'
	rename var7 `cat'_`use'_coal_`i'
	rename var8 `cat'_`use'_coke_`i'
	rename var9 `cat'_`use'_other_`i'
	rename var10 `cat'_`use'_shipment_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*

	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 2_1
*********************************
local cat = "firstuse"
local use = "nonfuel"
local lastcol = "M"
local firstrow = 9
local lastrow = 96
local name_rows = 8
forvalues i = 1(1)2 {
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
	}
	import excel using "$MECSraw/`year'/Table2_`i'.xls", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 2.`i'") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	local num = 0
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
		quietly sum `var', meanonly
		if `r(N)' == 0 {
			disp("`var' A label column")
			drop `var'
		}
		else {
			local num = `num' + 1
			rename `var' var`num'
		}
	}
	*
	rename A naics
	rename B industry
	rename var1 `cat'_`use'_tot_`i'
	rename var2 `cat'_`use'_resdfuel_`i'
	rename var3 `cat'_`use'_distfuel_`i'
	rename var4 `cat'_`use'_gas_`i'
	rename var5 `cat'_`use'_hgl_`i'
	rename var6 `cat'_`use'_coal_`i'
	rename var7 `cat'_`use'_coke_`i'
	rename var8 `cat'_`use'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*



*********************************
* Table 3_1
*********************************
local cat = "firstuse"
local use = "fuel"
local lastcol = "O"
local firstrow = 9
local lastrow = 96
local name_rows = 8
forvalues i = 1(1)2 {
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
	}
	import excel using "$MECSraw/`year'/Table3_`i'.xls", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 3.`i'") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	local num = 0
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
		quietly sum `var', meanonly
		if `r(N)' == 0 {
			disp("`var' A label column")
			drop `var'
		}
		else {
			local num = `num' + 1
			rename `var' var`num'
		}
	}
	*
	rename A naics
	rename B industry
	rename var1 `cat'_`use'_tot_`i'
	rename var2 `cat'_`use'_elec_`i'
	rename var3 `cat'_`use'_resdfuel_`i'
	rename var4 `cat'_`use'_distfuel_`i'
	rename var5 `cat'_`use'_gas_`i'
	rename var6 `cat'_`use'_hgl_`i'
	rename var7 `cat'_`use'_coal_`i'
	rename var8 `cat'_`use'_coke_`i'
	rename var9 `cat'_`use'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 4_1 - 4_2 
*********************************
local cat = "offsiteProd"
local lastcol = "O"
local firstrow = 9
local lastrow = 96
local name_rows = 8 
forvalues i = 1(1)2 {
	if `i' == 2 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
	}
	import excel using "$MECSraw/`year'/Table4_`i'.xls", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 4.`i'") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	local num = 0
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
		quietly sum `var', meanonly
		if `r(N)' == 0 {
			disp("`var' A label column")
			drop `var'
		}
		else {
			local num = `num' + 1
			rename `var' var`num'
		}
	}
	*
	rename A naics
	rename B industry
	rename var1 `cat'_tot_`i'
	rename var2 `cat'_elec_`i'
	rename var3 `cat'_resdfuel_`i'
	rename var4 `cat'_distfuel_`i'
	rename var5 `cat'_gas_`i'
	rename var6 `cat'_hgl_`i'
	rename var7 `cat'_coal_`i'
	rename var8 `cat'_coke_`i'
	rename var9 `cat'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*



*********************************
* Table 7_6 & 7_9
*********************************
local cat = "Purchased"
local use = "fuel"
local lastcol = "K"
local lastrow = 95
local firstrow = 8
local name_rows = 8
foreach j of num 6 9 {
	if `j' == 9 {
		local firstrow = `firstrow' - 1
		local lastrow = `lastrow' - 1
	}
	import excel using "$MECSraw/`year'/Table7_`j'.xls", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 7.`j'") clear

	local i = "quat"
	if `j' == 9 {
		local i = "usd"
	}
	*
	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	local num = 0
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
		quietly sum `var', meanonly
		if `r(N)' == 0 {
			disp("`var' A label column")
			drop `var'
		}
		else {
			local num = `num' + 1
			rename `var' var`num'
		}
	}
	*
	rename A naics
	rename B industry
	rename var1 `cat'_tot_`i'
	rename var2 `cat'_elec_`i'
	rename var3 `cat'_resdfuel_`i'
	rename var4 `cat'_distfuel_`i'
	rename var5 `cat'_gas_`i'
	rename var6 `cat'_hgl_`i'
	rename var7 `cat'_coal_`i'
	rename var8 `cat'_coke_`i'
	rename var9 `cat'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 11_1 
*********************************
local lastcol = "G"
local lastrow = 94
local firstrow = 7
local name_rows = 8
	import excel using "$MECSraw/`year'/Table11_1.xls", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 11.1") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4' (million kWh)"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4' (million kWh)"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4' (million kWh)"
		}
		else {
			label var `var' "`l4' (million kWh)"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C Purchased_elec
	rename D TransferIn_elec
	rename E OnsiteGen_elec
	rename F TransferOff_elec
	rename G NetDemand_elec


	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 
	
*********************************
* SAVE
*********************************
* save for this year
gen year = `year'
order order naics industry year
sort order
save "$MECSenergy/MECS`year'.dta", replace



